package com.jiedeng.mapper.provider;

import java.util.List;

/**
 * 报表sql
 *
 * @author Administrator
 */
public class ReportSqlProvider {
    /**
     * 明细表单位得分sql
     *
     * @param projectId
     * @return
     */
    public String detailedReportSql(Long projectId) {
        String sql = "SELECT " +
                "tc.Corp_Caption, " +
                "tpc.Corp_Prop_ID, " +
                "tt.Start_Time, " +
                "tt.Caption, " +

                "sum(CAST( tcc.Scoring as  DECIMAL)) Scoring " +
                "FROM " +
                " t_project_corp tpc " +
                "LEFT JOIN t_corp tc ON tc.Corp_ID = tpc.Corp_ID " +
                "LEFT JOIN t_task tt on tt.Project_ID = tpc.Project_ID " +
                "LEFT JOIN t_check tck on tt.TaskID = tck.Task_ID " +
                "LEFT JOIN t_checkcount tcc on tck.ID= tcc.Check_ID " +
                "WHERE " +
                " tpc.Project_ID =  " + projectId +
                " and tpc.Corp_Prop_ID in (2,4,5) and tck.Ispub = 1 and tcc.Scoring is not null " +
                "GROUP BY tt.TaskID,tc.Corp_Caption,tt.Start_Time,tt.Caption,tpc.Corp_Prop_ID " +
                "ORDER BY Start_Time ,Corp_Prop_ID";
        return sql;
    }

    public String detailedReportSql2(Long projectId) {
        String sql = " select  " +
                " a.Corp_ID, " +
                " a.Corp_Caption, " +
                " a.Corp_Prop_ID, " +
                " b.Start_Time, " +
                " b.Caption, " +
                " a.Weighting, " +
                " CASE " +
                "  WHEN b.Scoring IS NULL THEN " +
                "   a.Weighting " +
                "  ELSE " +
                "   a.Weighting - b.Scoring " +
                "  END Scoring " +
                " from ( " +
                " " +
                " SELECT " +
                " tcir.id tcir_tcir, " +
                " tpc.Corp_Prop_ID, " +
                " tc.Corp_ID, " +
                " tc.Corp_Caption, " +
                " SUM(tcir.Weighting) Weighting " +
                " FROM " +
                " t_project_corp tpc " +
                " LEFT JOIN t_corp tc ON tc.Corp_ID = tpc.Corp_ID " +
                " LEFT JOIN t_checkitemrule tcir ON tcir.Corp_Prop_ID = tc.Corp_Prop_ID " +
                " WHERE " +
                " tpc.Project_ID =  " +projectId+
                " AND tpc.Corp_Prop_ID IN (2, 4, 5) " +
                " AND tcir.Weighting IS NOT NULL " +
                " AND tcir.Weighting != '' " +
                " GROUP BY " +
                " tc.Corp_ID " +
                " ) a LEFT JOIN " +
                "( " +
                " SELECT " +
                " tt.Start_Time, " +
                " tt.Caption, " +
                " tcc.Corp_ID, " +
                " sum(tcc.Scoring) Scoring " +
                "FROM " +
                " t_project_corp tpc " +
                " LEFT JOIN t_task tt ON tt.Project_ID = tpc.Project_ID " +
                " LEFT JOIN t_check tck ON tt.TaskID = tck.Task_ID " +
                " LEFT JOIN t_checkcount tcc ON tcc.Check_ID = tck.ID and tcc.Corp_ID = tpc.Corp_ID " +
                " WHERE " +
                " tpc.Project_ID =  " +projectId+
                " AND tpc.Corp_Prop_ID IN (2, 4, 5) " +
                " AND tck.Ispub = 1 " +
                " AND tcc.Scoring IS NOT NULL " +
                " GROUP BY " +
                " tt.Start_Time, " +
                " tcc.Corp_ID " +
                " ) b on a.Corp_ID = b.Corp_ID " +
                " ORDER  BY  b.Start_Time,a.Corp_Prop_ID ";
                
        return sql;
    }
    public String detailedReportSql3(Long projectId) {
        String sql = " SELECT " +
                " a.Corp_ID, " +
                " a.Corp_Caption, " +
                " a.Corp_Prop_ID, " +
                " c.Start_Time, " +
                " a.Weighting, " +
                " CASE " +
                " WHEN b.Scoring IS NULL THEN " +
                " a.Weighting " +
                " ELSE " +
                " a.Weighting - b.Scoring " +
                " END Scoring " +
                " FROM " +
                " ( " +
                "  SELECT " +
                "   tcir.id tcir_tcir, " +
                "   tpc.Corp_Prop_ID, " +
                "   tc.Corp_ID, " +
                "   tc.Corp_Caption, " +
                "   SUM(tcir.Weighting) Weighting " +
                "  FROM " +
                "   t_project_corp tpc " +
                "  LEFT JOIN t_corp tc ON tc.Corp_ID = tpc.Corp_ID " +
                "  LEFT JOIN t_checkitemrule tcir ON tcir.Corp_Prop_ID = tc.Corp_Prop_ID " +
                "  WHERE " +
                "   tpc.Project_ID =  " +projectId+
                "  AND tpc.Corp_Prop_ID IN (2, 4, 5) " +
                "  AND tcir.Weighting IS NOT NULL " +
                "  AND tcir.Weighting != '' " +
                "  GROUP BY " +
                "   tc.Corp_ID " +
                " ) a " +
                " LEFT JOIN ( " +
                " SELECT " +
                "  tt.Start_Time " +
                " FROM " +
                "  t_project_corp tpc " +
                " LEFT JOIN t_task tt ON tt.Project_ID = tpc.Project_ID " +
                " LEFT JOIN t_check tck ON tt.TaskID = tck.Task_ID " +
                " LEFT JOIN t_checkcount tcc ON tcc.Check_ID = tck.ID " +
                " AND tcc.Corp_ID = tpc.Corp_ID " +
                " WHERE " +
                "  tpc.Project_ID =  " +projectId+
                " AND tpc.Corp_Prop_ID IN (2, 4, 5) " +
                " AND tck.Ispub = 1 " +
                " AND tt.IsClose = 1 " +
                " AND tcc.Scoring IS NOT NULL " +
                " GROUP BY " +
                "  tt.Start_Time " +
                ") c ON 1 = 1 " +
                " LEFT JOIN ( " +
                " SELECT " +
                "  tt.Start_Time, " +

                "  tt.Caption, " +
                "  tcc.Corp_ID, " +
                "  sum(tcc.Scoring) Scoring " +
                " FROM " +
                "  t_project_corp tpc " +
                " LEFT JOIN t_task tt ON tt.Project_ID = tpc.Project_ID " +
                " LEFT JOIN t_check tck ON tt.TaskID = tck.Task_ID " +
                " LEFT JOIN t_checkcount tcc ON tcc.Check_ID = tck.ID " +
                " AND tcc.Corp_ID = tpc.Corp_ID " +
                " WHERE " +
                "  tpc.Project_ID = 2163 " +
                " AND tpc.Corp_Prop_ID IN (2, 4, 5) " +
                " AND tck.Ispub = 1 " +
                " AND tt.IsClose = 1 " +
                " AND tcc.Scoring IS NOT NULL " +
                " GROUP BY " +
                "  tt.Start_Time, " +
                "  tcc.Corp_ID " +
                " ) b ON a.Corp_ID = b.Corp_ID " +
                " AND b.Start_Time = c.Start_Time " +
                " ORDER BY " +
                " c.Start_Time, " +
                " a.Corp_Prop_ID " ;
        return sql;
    }

    public String getCorpScoringByProjectIdAndCorpIdAndTimeSql(Long corpPropId,Long projectId,Long corpId,String startTime){
        String sql=" SELECT " +
                " a.Post_ID, " +
                " a.Weighting, " +
                " CASE " +
                " WHEN b.Scoring IS NULL THEN " +
                " a.Weighting " +
                " ELSE " +
                " a.Weighting - b.Scoring " +
                " END Scoring " +
                " FROM " +
                " ( " +
                "  SELECT " +
                "   SUM(tcir.Weighting) Weighting, " +
                "   tcir.Post_ID " +
                "  FROM " +
                "   t_checkitemrule tcir " +
                "  WHERE " +
                "   tcir.Corp_Prop_ID =  " +corpPropId+
                " and tcir.Post_ID >0 " +
                " GROUP BY " +
                "   tcir.Post_ID " +
                " ) a " +
                " LEFT JOIN ( " +
                " SELECT " +
                "  sum(tcc.Scoring) Scoring, " +
                "  tcc.Post_ID " +
                " FROM " +
                "  t_task tt " +
                " LEFT JOIN t_check tck ON tt.TaskID = tck.Task_ID " +
                " LEFT JOIN t_checkcount tcc ON tcc.Check_ID = tck.ID " +
                " WHERE " +
                "  tt.Project_ID =  " +projectId+
                " AND tcc.Corp_Prop_ID =  " + corpPropId+
                " AND tck.Ispub = 1 " +
                " AND tt.IsClose = 1 " +
                " AND tcc.Scoring IS NOT NULL " +
                " AND tcc.Corp_ID =  " +corpId+
                " AND tt.Start_Time = '" +startTime+"' " +
                " GROUP BY " +
                "  tcc.Post_ID " +
                ") b ON a.Post_ID = b.Post_ID";
        return sql;
    }

    /**
     * 明细表单位人员得分表sql
     *
     * @param projectId
     * @return
     */
    public String detailedCorpPersonReportSql(Long projectId, Long corpId) {
        String sql = "SELECT " +
                " a.TaskID, " +
                " a.Corp_Prop_ID, " +
                " a.Start_Time, " +
                " a.Caption, " +
                " sum(CAST(a.Scoring AS DECIMAL)) Scoring, " +
                " tcp.Post_Caption, " +
                " tcp.Post_ID " +
                " FROM " +
                " t_corp_post tcp " +
                " INNER JOIN (SELECT " +
                " tt.TaskID, " +
                " tpc.Corp_Prop_ID, " +
                " tt.Start_Time, " +
                " tt.Caption, " +
                " tcc.Scoring, " +
                "  CASE tpc.Corp_Prop_ID " +
                "  WHEN 2 THEN 92 " +
                "  WHEN 4 THEN 94 " +
                "  WHEN 5 THEN 95 " +
                "   ELSE NULL END x " +
                " FROM " +
                " t_project_corp tpc " +
                " LEFT JOIN t_task tt ON tt.Project_ID = tpc.Project_ID " +
                " LEFT JOIN t_check tck ON tt.TaskID = tck.Task_ID " +
                " LEFT JOIN t_checkcount tcc ON tck.ID = tcc.Check_ID " +
                " WHERE " +
                " tpc.Corp_ID =  " + corpId +
                " AND tt.IsClose = 1 " +
                " AND tpc.Project_ID = " + projectId +
                " AND tpc.Corp_Prop_ID IN (2, 4, 5) and tck.Ispub = 1 and tcc.Scoring is not null " +
                " ) a ON  " +
                "  instr(tcp.Post_ID,a.x)=1  " +
                " GROUP BY  a.TaskID, a.Start_Time, a.Caption, a.Corp_Prop_ID ,tcp.Post_Caption,tcp.Post_ID " +
                " ORDER BY  Start_Time , Corp_Prop_ID,tcp.Post_ID  ";
        return sql;
    }

    public String detailedCorpPersonReportSql2(Long projectId, Long corpId) {

       String sql=" SELECT " +
               " a.Post_Caption, " +
               " a.Post_ID, " +
               " b.Start_Time, " +
               " a.Weighting, " +
               " CASE " +
               " WHEN b.Scoring IS NULL THEN " +
               " a.Weighting " +
               " ELSE " +
               " a.Weighting - b.Scoring " +
               " END Scoring " +
               " FROM " +
               " ( " +
               "  SELECT " +
               "   tcp.Post_ID, " +
               "   tcp.Post_Caption, " +
               "   sum(tcir.Weighting) Weighting " +
               "  FROM " +
               "   t_corp_post tcp " +
               "  INNER JOIN ( " +
               "   SELECT " +
               "    tpc.Corp_Prop_ID, " +
               "    CASE tpc.Corp_Prop_ID " +
               "   WHEN 2 THEN " +
               "    92 " +
               "   WHEN 4 THEN " +
               "    94 " +
               "   WHEN 5 THEN " +
               "    95 " +
               "   ELSE " +
               "    NULL " +
               "   END x " +
               "   FROM " +
               "    t_project_corp tpc " +
               "   WHERE " +
               "    tpc.Corp_ID =  " +corpId+
               "   AND tpc.Project_ID =  " +projectId+
               "   AND tpc.Corp_Prop_ID IN (2, 4, 5) " +
               "  ) a ON instr(tcp.Post_ID, a.x) = 1 " +
               "  LEFT JOIN t_checkitemrule tcir ON tcir.Post_ID = tcp.Post_ID " +
               "  WHERE " +
               "   tcir.Weighting IS NOT NULL " +
               "  AND tcir.Weighting != '' " +
               "  GROUP BY " +
               "   tcir.Post_ID " +
               " ) a " +
               " LEFT JOIN ( " +
               " SELECT " +
               "  tcc.Post_ID, " +
               "  tt.Start_Time, " +
               "  sum(tcc.Scoring) Scoring " +
               " FROM " +
               "  t_task tt " +
               " LEFT JOIN t_check tck ON tt.TaskID = tck.Task_ID " +
               " LEFT JOIN t_checkcount tcc ON tck.id = tcc.Check_ID " +
               " WHERE " +
               "  tt.Project_ID =  " +projectId+
               " AND tck.Ispub = 1 " +
               " AND tcc.Corp_ID =  " +corpId+
               " GROUP BY " +
               "  tcc.Post_ID, " +
               "  tt.Start_Time " +
               " ) b ON a.Post_ID = b.Post_ID " +
               " ORDER BY  b.Start_Time , a.Post_ID  " ;

        return sql;
    }
    public String detailedCorpPersonReportSql3(Long projectId, Long corpId) {
        String sql="SELECT " +
                " a.Post_Caption, " +
                " a.Post_ID, " +
                " t.Start_Time, " +
                " a.Weighting, " +
                " CASE " +
                "WHEN b.Scoring IS NULL THEN " +
                " a.Weighting " +
                "ELSE " +
                " a.Weighting - b.Scoring " +
                "END Scoring " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "   tcp.Post_ID, " +
                "   tcp.Post_Caption, " +
                "   sum(tcir.Weighting) Weighting " +
                "  FROM " +
                "   t_corp_post tcp " +
                "  INNER JOIN ( " +
                "   SELECT " +
                "    tpc.Corp_Prop_ID, " +
                "    CASE tpc.Corp_Prop_ID " +
                "   WHEN 2 THEN " +
                "    92 " +
                "   WHEN 4 THEN " +
                "    94 " +
                "   WHEN 5 THEN " +
                "    95 " +
                "   ELSE " +
                "    NULL " +
                "   END x " +
                "   FROM " +
                "    t_project_corp tpc " +
                "   WHERE " +
                "    tpc.Corp_ID =  " +corpId+
                "   AND tpc.Project_ID =  " +projectId+
                "   AND tpc.Corp_Prop_ID IN (2, 4, 5) " +
                "  ) a ON instr(tcp.Post_ID, a.x) = 1 " +
                "  LEFT JOIN t_checkitemrule tcir ON tcir.Post_ID = tcp.Post_ID " +
                "  WHERE " +
                "   tcir.Weighting IS NOT NULL " +
                "  AND tcir.Weighting != '' " +
                "  GROUP BY " +
                "   tcir.Post_ID " +
                " ) a " +
                "LEFT JOIN ( " +
                "SELECT " +
                "  tt.Start_Time " +
                "  " +
                " FROM " +
                "  t_task tt " +
                " LEFT JOIN t_check tck ON tt.TaskID = tck.Task_ID " +
                " LEFT JOIN t_checkcount tcc ON tck.id = tcc.Check_ID " +
                " WHERE " +
                "  tt.Project_ID =  " +projectId+
                " AND tck.Ispub = 1 " +
                " AND tt.IsClose = 1 " +
                " GROUP BY " +
                "  " +
                "  tt.Start_Time) t on 1=1 " +
                " LEFT JOIN ( " +
                " SELECT " +
                "  tcc.Post_ID, " +
                "  tt.Start_Time, " +
                "  sum(tcc.Scoring) Scoring " +
                " FROM " +
                "  t_task tt " +
                " LEFT JOIN t_check tck ON tt.TaskID = tck.Task_ID " +
                " LEFT JOIN t_checkcount tcc ON tck.id = tcc.Check_ID " +
                " WHERE " +
                "  tt.Project_ID =  " +projectId+
                " AND tck.Ispub = 1 " +
                " AND tt.IsClose = 1 " +
                " AND tcc.Corp_ID =  " +corpId+
                " GROUP BY " +
                "  tcc.Post_ID, " +
                "  tt.Start_Time " +
                " " +
                " ) b ON a.Post_ID = b.Post_ID and b.Start_Time = t.Start_Time " +
                " ORDER BY " +
                " t.Start_Time, " +
                " a.Post_ID ";
        return sql;
    }
    /**
     * 统计表获取检查详情
     *
     * @param projectId
     * @param postId
     * @return
     */
    public String statisticsReportInspectSql(Long projectId, Long postId) {
        String sql = "SELECT DISTINCT " +
                " tcir.Weighting,tci.checkItem_Demand,tc.ID,tc.Expression_Desc,tc.Check_time " +
                " FROM " +
                " t_task tt " +
                " LEFT JOIN t_check tc ON tt.TaskID = tc.Task_ID " +
                " LEFT JOIN t_checkitem tci ON tci.checkItem_ID = tc.checkItem_ID " +
                " LEFT JOIN t_checkitemrule tcir on tcir.checkItem_ID = tci.checkItem_ID " +
                " WHERE " +
                " tc.Ispub = 1 " +
                " AND Weighting IS NOT NULL " +
                " AND Weighting != '' " +
                " AND tt.IsClose = 1 " +
                " and  tcir.Post_ID = " + postId +
                " and tt.Project_ID =  " + projectId;
        return sql;
    }

    /**
     * 检查数据sql
     *
     * @param taskId
     * @param specId
     * @return
     */
    public String getCheckItemSql(String taskId, Integer specId) {
        String sql = " select tck.checkItem_ID, tck.checkItem_Demand " +
//                "-- ,tcr.UnplusDemo,tcr.Weighting  " +
                " , tc.Expression_Desc, tc.id " +
                " from t_checkitem tck " +
//                "-- LEFT JOIN t_checkitemrule tcr on tck.checkItem_ID= tcr.checkItem_ID " +
                " LEFT JOIN (select * from t_check tc  where tc.Ispub = 1 and " +
                " tc.Task_ID = '" + taskId + "' " +
                " ) tc  on  tck.checkItem_ID = tc.checkItem_ID " +
                " where   tck.Spec_ID =  " + specId +
                " ORDER BY tck.checkItem_ID ";
        return sql;
    }

    public String findCheckItemRuleCountSql(Long postId, Long corpId, List<String> checkIds) {
        String sql = " select tcir.Weighting, " +
                " case when tcc.Scoring is  null  " +
                " then  tcir.Weighting " +
                " else " +
                "  tcir.Weighting - tcc.Scoring end Scoring " +
                " FROM " +
                " ( select * from t_checkitemrule  where Post_ID = " + postId +
                " and Weighting is not null and Weighting !='' " +
                " ) tcir LEFT JOIN   " +
                " ( SELECT " +
                " tcc.*,tc.checkItem_ID " +
                " FROM " +
                "t_check tc  " +
                "LEFT JOIN t_checkcount tcc on tcc.Check_ID=tc.ID " +
                "   WHERE  tcc.Post_ID = " + postId +
                " and tcc.Scoring is not null " +
                " and tcc.Corp_ID = " + corpId;
        if (checkIds != null && checkIds.size() > 0) {
            sql += "  and  tcc.Check_ID in (";
            int i = 0;
            for (String checkId : checkIds) {
                if (i != 0) {
                    sql += " , ";
                }
                sql += " '" + checkId + "' ";
                i++;
            }

            sql += ") ";
        }

        sql += " ) " +
                " tcc on tcir.Post_ID = tcc.Post_ID and tcir.Corp_Prop_ID = tcc.Corp_Prop_ID " +
                " AND tcir.checkItem_ID = tcc.checkItem_ID ";
        return sql;
    }

    public String findCheckItemRuleCountSql2(Long postId, Long corpId,  Integer propId, List<String> checkIds) {
        String sql = " select tcir.Weighting, " +
                " case when tcc.Scoring is  null  " +
                " then  tcir.Weighting " +
                " else " +
                "  tcir.Weighting - tcc.Scoring end Scoring " +
                " FROM " +
                " ( select * from t_checkitemrule  where Post_ID = " + postId +
                " and Weighting is not null and Weighting !='' " +
                " ) tcir " +
                " LEFT JOIN t_checkitem tci ON tci.checkItem_ID = tcir.checkItem_ID " +
                " LEFT JOIN   " +
                " ( SELECT " +
                " tcc.*,tc.checkItem_ID " +
                " FROM " +
                "t_check tc  " +
                "LEFT JOIN t_checkcount tcc on tcc.Check_ID=tc.ID " +
                "   WHERE  tcc.Post_ID = " + postId +
                " and tcc.Scoring is not null " +
                " and tcc.Corp_ID = " + corpId;
        if (checkIds != null && checkIds.size() > 0) {
            sql += "  and  tcc.Check_ID in (";
            int i = 0;
            for (String checkId : checkIds) {
                if (i != 0) {
                    sql += " , ";
                }
                sql += " '" + checkId + "' ";
                i++;
            }

            sql += ") ";
        }

        sql += " ) " +
                " tcc on tcir.Post_ID = tcc.Post_ID and tcir.Corp_Prop_ID = tcc.Corp_Prop_ID " +
                " AND tcir.checkItem_ID = tcc.checkItem_ID " +
                " where tci.prop_Id = " +propId +" ";
        return sql;
    }
}
